﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
// ADO.NET ......... api for database communication
// ADO.NET stands for ActiveX Data Objects for .NET
using System.Data;
using System.Data.SqlClient;


namespace WebApplication2
{
    public partial class Test : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        // What should happen when the button "btnSubmit" is clicked?
        // EVENT HANDLER
        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            #region Data Capture
            string username = txtUsername.Text;
            string password = txtPassword.Text;
            string firstName = txtFirstName.Text;
            string lastName = txtLastName.Text;
            string phone = txtPhone.Text;

            int age = int.Parse(txtAge.Text);
            DateTime dateOfBirth = DateTime.Parse(txtDateOfBirth.Text);
            #endregion

            // First check if the username is valid
            // Execute SELECT query to see if there is a matching username in the DB

            string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=SampleDB;Integrated Security=True";

            SqlConnection myConn = new SqlConnection(connectionString);
            myConn.Open();

            string query = "select COUNT(*) from Users where Username='" + txtUsername.Text + "'";

            // You will get back a number ....... Returning a SCALAR value
            SqlCommand myCommand = new SqlCommand();
            myCommand.CommandText = query;
            myCommand.Connection = myConn;

            object result = myCommand.ExecuteScalar();  // Result MUST be a ONE SINGLE value ...... not full records

            int count = int.Parse(result.ToString());

            if (count == 0)
            {
                // No matching records with the username ....... username is AVAILABLE
                // So, execute the INSERT
                // If username is valid, then execute SQL
                #region SQL Execution


                try
                {
                    // Saving the data into a data source (text file, database, etc.)


                    //// Create a new connection
                    //SqlConnection connection = new SqlConnection(connectionString);

                    //// Open the connection
                    //connection.Open();

                    // Prepare SQL command
                    SqlCommand command = new SqlCommand();
                    command.Connection = myConn;

                    command.CommandText = "insert into Users([Username], [Password], [FirstName], [LastName], [Age], [DateOfBirth], [PhoneNumber]) values('"
                                            + username + "', '" + password + "', '" + firstName + "', '" + lastName + "', '" + age + "', '" + dateOfBirth + "', '" + phone + "');";

                    // Execute SQL command
                    command.ExecuteNonQuery(); // INSERT, UPDATE, DELETE ....... Non-query commands

                    //// Close connection
                    //connection.Close();

                    // Page navigation ....... RESPONSE
                    Response.Redirect("ThankYou.aspx");
                }
                catch (Exception ex)
                {
                    Response.Write(ex.Message);
                }

                #endregion
            }
            else
            {
                // Count > 0 ....... there is a matching record with the same username ...... username is NOT available ....
                // Inform the user to enter a different username
                lblUsernameValid.Text = "Username is already taken. Please enter a different username.";
            }

            myConn.Close();
        }
    }
}